package com.xinchuang.service.impl;

import com.alibaba.fastjson.JSON;
import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import com.baomidou.mybatisplus.core.conditions.query.LambdaQueryWrapper;
import com.xinchuang.comment.KeyWord;
import com.xinchuang.comment.R;
import com.xinchuang.domain.type.JlNameEnum;
import com.xinchuang.domain.type.JlQuotationEnum;
import com.xinchuang.domain.vo.jl.*;
import com.xinchuang.entity.JlQuotationPrice;
import com.xinchuang.mapper.JlQuotationPriceMapper;
import com.xinchuang.service.ExportJlOutQuotationService;
import com.xinchuang.service.IMoldSelectionService;
import com.xinchuang.utils.BigdecimalUtil;
import com.xinchuang.utils.DateUtils;
import com.xinchuang.utils.ExcelUtil;
import com.xinchuang.utils.PoiUtils;
import lombok.RequiredArgsConstructor;
import lombok.extern.slf4j.Slf4j;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.ss.util.RegionUtil;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import javax.servlet.http.HttpServletResponse;
import java.io.File;
import java.text.DecimalFormat;
import java.util.ArrayList;
import java.util.EnumSet;
import java.util.List;
import java.util.Objects;

/**
 * @Description:导出吉利对外报价
 * @Author: DuanZG
 * @Date: 2023/10/16 22:24
 */
@Slf4j
@Service
@RequiredArgsConstructor
public class ExportJlOutQuotationServiceImpl implements ExportJlOutQuotationService {

    private final KeyWord keyWord;

    @Autowired
    private IMoldSelectionService iMoldSelectionService;

    @Autowired
    private JlQuotationPriceMapper jlQuotationPriceMapper;

    @Override
    public String exportJlOutQuotation(String data, HttpServletResponse res) {
        R r = JSON.parseObject(data, R.class);
        JlDataVo jlDataVo = JSON.parseObject(JSON.toJSONString(r.getData()), JlDataVo.class);
        String moldSelectionId = jlDataVo.getMoldSelectionId();
        String projectName = iMoldSelectionService.getProjectName(moldSelectionId);
        // 文件存储根路径
        String fileName = projectName + "项目-吉利模具价格明细-" + DateUtils.dateTimeNow("yyyyMMdd-SSS");
        //返回地址
        String resultPath = "file" + File.separator + fileName + ".xlsx";
        String filePath = keyWord.getFileUrl() + File.separator + resultPath;
        createExcelFile(jlDataVo, filePath, fileName);
        return resultPath;
    }

    public File createExcelFile(JlDataVo vo, String filePath, String fileName) {
        Workbook workbook = new XSSFWorkbook();
        List<JlDetailVo> detailVoList = vo.getDataList();
        for (int i = 0; i < detailVoList.size(); i++) {
            JlDetailVo jlDetailVo = detailVoList.get(i);
            List<JlQuotationPrice> debugFeeList = jlQuotationPriceMapper.selectList(new LambdaQueryWrapper<JlQuotationPrice>()
                    .eq(JlQuotationPrice::getMoldSelectionId, vo.getMoldSelectionId())
                    .eq(JlQuotationPrice::getDetailId, jlDetailVo.getSubTableId())
                    .eq(JlQuotationPrice::getType, "调试"));
            Sheet sheet = workbook.createSheet("价格明细表" + (i + 1));
            //全局设置 单元格的宽度
            sheet.setColumnWidth(0, 1500); // 设置第一列的宽度
            sheet.setColumnWidth(1, 3900); // 设置列的宽度
            sheet.setColumnWidth(2, 5500); // 设置列的宽度
            sheet.setColumnWidth(3, 7000); // 设置列的宽度
            sheet.setColumnWidth(4, 7000); // 设置列的宽度
            sheet.setColumnWidth(5, 5500); // 设置列的宽度
            sheet.setColumnWidth(6, 5100); // 设置列的宽度
            sheet.setColumnWidth(7, 5500); // 设置列的宽度
            sheet.setColumnWidth(8, 5500); // 设置列的宽度

            // 设置标题行
            Row titleRow = sheet.createRow(0);
            Cell cellTitle = titleRow.createCell(0);
            titleRow.setHeight((short) 850);
            cellTitle.setCellValue("E171车型外协件注塑模具价格明细表-第17包（人民币/元，含13%增值税）");
            CellStyle titleStyle = createCellStyle(workbook, "微软雅黑", 18, true, false, HorizontalAlignment.CENTER);
            setCellStyle(cellTitle, titleStyle, "center");
            mergeCells(sheet, 0, 0, 0, 8);

            CellStyle rowStyle = createCellStyle(workbook, "微软雅黑", 12, true, false, HorizontalAlignment.CENTER);
            CellStyle cellStyle = createCellStyle(workbook, "微软雅黑", 11, false, false, HorizontalAlignment.CENTER);

            //第二行
            // 设置零件名称、零件编号、注塑机吨位
            Row secRow = sheet.createRow(1);
            secRow.setHeight((short) 560);
            createCell(secRow, 0, "零件名称", rowStyle);
            //零部件名称合并单元格
            mergeCells(sheet, 1, 1, 0, 1);
            createCell(secRow, 2, jlDetailVo.getNameOfProduct(), rowStyle);
            mergeCells(sheet, 1, 1, 2, 3);
            createCell(secRow, 4, "零件编号", rowStyle);
            createCell(secRow, 5, jlDetailVo.getCodeOfProduct(), rowStyle);
            mergeCells(sheet, 1, 1, 5, 6);
            createCell(secRow, 7, "注塑机吨位", rowStyle);
            createCell(secRow, 8, jlDetailVo.getPressTon(), rowStyle);

            JlDetailVo detailData = jlDetailVo.getDetailData();
            int rowNum = 2;
            //各明细小计
            double rawMaterialSubTotal = 0; //原材料
            double standardComponentSubTotal = 0; //标准件
            double artificialFeeSubTotal = 0; //人工
            double manufacturingFeeSubTotal = 0; //制造
            double assemblyFeeSubTotal = 0; //装配
            double debugFeeSubTotal = 0; //调试
            double measureFeeSubTotal = 0; //测量
            double transportFeeSubTotal = 0; //运输

            int rawMaterialsize = 0;
            int standardComponentsize = 0;
            int artificialFeesize = 0;
            int manufacturingFeesize = 0;
            int assemblyFeesize = 0;
            int debugFeesize = debugFeeList.size();
            int measureFeesize = 0;
            int transportFeesize = 0;

            List<JlQuotationEnum> jlQuotationEnumList = new ArrayList<>(EnumSet.allOf(JlQuotationEnum.class));
            for (int k = 0; k < jlQuotationEnumList.size(); k++) {
                Row subtitleRow = sheet.createRow(rowNum);
                subtitleRow.setHeight((short) 490);
                JlQuotationEnum jlQuotationEnum = jlQuotationEnumList.get(k);
                String name = jlQuotationEnum.getName();
                String sequence = String.valueOf(k + 1);
                if ("原材料".equals(name)) {
                    createCell(subtitleRow, 0, "序号", rowStyle);
                    createCell(subtitleRow, 1, "价格明细", rowStyle);
                    createCell(subtitleRow, 2, "名称", rowStyle);
                    createCell(subtitleRow, 3, "品牌+牌号", cellStyle);
                    createCell(subtitleRow, 4, "规格尺寸（MM）", cellStyle);
                    createCell(subtitleRow, 5, "重量（KG）", cellStyle);
                    rowNum++;
                } else if ("标准件".equals(name)) {
                    createCell(subtitleRow, 0, sequence, rowStyle);
                    createCell(subtitleRow, 1, name, rowStyle);
                    createCell(subtitleRow, 2, "名称", rowStyle);
                    createCell(subtitleRow, 3, "品牌", cellStyle);
                    createCell(subtitleRow, 4, "型号", cellStyle);
                    createCell(subtitleRow, 5, "数量（个/套）", cellStyle);
                    rowNum++;
                } else if ("人工".equals(name) ||
                        "制造".equals(name) ||
                        "装配".equals(name) ||
                        "调试".equals(name) ||
                        "测量".equals(name)) {
                    createCell(subtitleRow, 0, sequence, rowStyle);
                    createCell(subtitleRow, 1, name, rowStyle);
                    createCell(subtitleRow, 2, "名称", rowStyle);
                    createCell(subtitleRow, 3, "工时（小时）", cellStyle);
                    rowNum++;
                } else if ("运输".equals(name)) {
                    createCell(subtitleRow, 0, sequence, rowStyle);
                    createCell(subtitleRow, 1, name, rowStyle);
                    createCell(subtitleRow, 2, "名称", rowStyle);
                    createCell(subtitleRow, 3, "重量（吨）", cellStyle);
                    rowNum++;
                }
                createCell(subtitleRow, 6, "单价（元/含税）", cellStyle);
                createCell(subtitleRow, 7, "金额（元/含税）", cellStyle);
                createCell(subtitleRow, 8, "小计（元/含税）", cellStyle);

                Row firstrow = ExcelUtil.getRow(sheet, rowNum);
                switch (name) {
                    case "原材料":
                        JSONObject rawMaterialObj = detailData.getRawMaterial();
                        List<RawMaterial> rawMaterials = JSONArray.parseArray(String.valueOf(rawMaterialObj.getJSONArray("dataList")), RawMaterial.class);
                        rawMaterialsize = rawMaterials.size();
                        createCell(firstrow, 0, sequence, rowStyle);
                        createCell(firstrow, 1, name, rowStyle);
                        for (RawMaterial rawMaterial : rawMaterials) {
                            Row row = ExcelUtil.getRow(sheet, rowNum);
                            row.setHeight((short) 490);
                            createCell(row, 2, rawMaterial.getItemName(), rowStyle);
                            createCell(row, 3, rawMaterial.getSpecification(), cellStyle);
                            createCell(row, 4, rawMaterial.getLength() + "*" +
                                    rawMaterial.getWidth() + "*" + rawMaterial.getHeight(), cellStyle);
                            createCell(row, 5, new DecimalFormat("0.000").format(rawMaterial.getWeight()), cellStyle);
                            createCell(row, 6, formatPrice(rawMaterial.getPrice()), cellStyle);
                            createCell(row, 7, formatPrice(rawMaterial.getTotalPrice()), cellStyle);
                            rowNum++;
                        }
                        //小计
                        rawMaterialSubTotal = rawMaterialObj.getDoubleValue("subTotal");
                        createCell(firstrow, 8, formatPrice(rawMaterialSubTotal), cellStyle);
                        break;
                    case "标准件":
                        JSONObject standardComponentObj = detailData.getStandardComponent();
                        List<StandardComponent> standardComponents = JSONArray.parseArray(String.valueOf(standardComponentObj.getJSONArray("dataList")), StandardComponent.class);
                        standardComponentsize = standardComponents.size();
                        for (StandardComponent standardComponent : standardComponents) {
                            Row row = ExcelUtil.getRow(sheet, rowNum);
                            row.setHeight((short) 490);
                            createCell(row, 2, standardComponent.getItemName(), rowStyle);
                            createCell(row, 3, String.valueOf(standardComponent.getSpecification()), cellStyle);
                            createCell(row, 4, String.valueOf(standardComponent.getModelNumber()), cellStyle);
                            createCell(row, 5, String.valueOf(standardComponent.getCount()), cellStyle);
                            createCell(row, 6, formatPrice(standardComponent.getPrice()), cellStyle);
                            createCell(row, 7, formatPrice(standardComponent.getTotalPrice()), cellStyle);
                            rowNum++;
                        }
                        //小计
                        standardComponentSubTotal = standardComponentObj.getDoubleValue("subTotal");
                        createCell(firstrow, 8, formatPrice(standardComponentSubTotal), cellStyle);
                        break;
                    case "人工":
                        JSONObject artificialFeeObj = detailData.getArtificialFee();
                        List<ArtificialFee> artificialFees = JSONArray.parseArray(String.valueOf(artificialFeeObj.getJSONArray("dataList")), ArtificialFee.class);
                        artificialFeesize = artificialFees.size();
                        for (ArtificialFee artificialFee : artificialFees) {
                            Row row = ExcelUtil.getRow(sheet, rowNum);
                            row.setHeight((short) 490);
                            createCell(row, 2, artificialFee.getItemName(), rowStyle);
                            createCell(row, 3, String.valueOf(artificialFee.getHour()), cellStyle);
                            createCell(row, 6, formatPrice(artificialFee.getPrice()), cellStyle);
                            createCell(row, 7, formatPrice(artificialFee.getTotalPrice()), cellStyle);
                            rowNum++;
                        }
                        //小计
                        artificialFeeSubTotal = artificialFeeObj.getDoubleValue("subTotal");
                        createCell(firstrow, 8, formatPrice(artificialFeeSubTotal), cellStyle);
                        break;
                    case "制造":
                        JSONObject manufacturingFeeObj = detailData.getManufacturingFee();
                        List<ManufacturingFee> manufacturingFees = JSONArray.parseArray(String.valueOf(manufacturingFeeObj.getJSONArray("dataList")), ManufacturingFee.class);
                        manufacturingFeesize = manufacturingFees.size();
                        for (ManufacturingFee manufacturingFee : manufacturingFees) {
                            Row row = ExcelUtil.getRow(sheet, rowNum);
                            row.setHeight((short) 490);
                            createCell(row, 2, manufacturingFee.getItemName(), rowStyle);
                            createCell(row, 3, String.valueOf(manufacturingFee.getHour()), cellStyle);
                            createCell(row, 6, formatPrice(manufacturingFee.getPrice()), cellStyle);
                            createCell(row, 7, formatPrice(manufacturingFee.getTotalPrice()), cellStyle);
                            rowNum++;
                        }
                        //小计
                        manufacturingFeeSubTotal = manufacturingFeeObj.getDoubleValue("subTotal");
                        createCell(firstrow, 8, formatPrice(manufacturingFeeSubTotal), cellStyle);
                        break;
                    case "装配":
                        JSONObject assemblyFeeObj = detailData.getAssemblyFee();
                        List<AssemblyFee> assemblyFees = JSONArray.parseArray(String.valueOf(assemblyFeeObj.getJSONArray("dataList")), AssemblyFee.class);
                        assemblyFeesize = assemblyFees.size();
                        for (AssemblyFee assemblyFee : assemblyFees) {
                            Row row = ExcelUtil.getRow(sheet, rowNum);
                            row.setHeight((short) 490);
                            createCell(row, 2, assemblyFee.getItemName(), rowStyle);
                            createCell(row, 3, String.valueOf(assemblyFee.getHour()), cellStyle);
                            createCell(row, 6, formatPrice(assemblyFee.getPrice()), cellStyle);
                            createCell(row, 7, formatPrice(assemblyFee.getTotalPrice()), cellStyle);
                            rowNum++;
                        }
                        //小计
                        assemblyFeeSubTotal = assemblyFeeObj.getDoubleValue("subTotal");
                        createCell(firstrow, 8, formatPrice(assemblyFeeSubTotal), cellStyle);
                        break;
                    case "调试":
                        JSONObject debugFee = detailData.getDebugFee();
                        for (JlQuotationPrice jlQuotationPrice : debugFeeList) {
                            Row row = ExcelUtil.getRow(sheet, rowNum);
                            row.setHeight((short) 490);
                            createCell(row, 2, jlQuotationPrice.getName(), rowStyle);
                            createCell(row, 3, BigdecimalUtil.stringTo1DecimalString(jlQuotationPrice.getNum()), cellStyle);
                            createCell(row, 6, "￥" + BigdecimalUtil.stringTo2DecimalString(jlQuotationPrice.getPrice()), cellStyle);
                            createCell(row, 7, "￥" + BigdecimalUtil.stringTo2DecimalString(jlQuotationPrice.getTotalPrice()), cellStyle);
                            rowNum++;
                        }
                        //小计
                        debugFeeSubTotal = debugFee.getDoubleValue("subTotal");
                        createCell(firstrow, 8, formatPrice(debugFeeSubTotal), cellStyle);
                        break;
//                        JSONObject tryoutObj = debugFee.getJSONObject("tryoutObj");
//
//                        JSONObject debugObj = debugFee.getJSONObject("debugObj");
//                        if ("试模".equals(jlNameEnum.getItemName())) {
//                            createCell(subtitleRow, 0, sequence, rowStyle);
//                            createCell(subtitleRow, 1, name, rowStyle);
//                            createCell(subtitleRow, 2, jlNameEnum.getItemName(), rowStyle);
//                            createCell(subtitleRow, 3, "试模次数（次）", cellStyle);
//                            createCell(subtitleRow, 6, "单价（元/含税）", cellStyle);
//                            createCell(subtitleRow, 7, "金额（元/含税）", cellStyle);
//                            JSONObject debugFee = detailData.getDebugFee();
//                            debugFeeSubTotal = debugFee.getDoubleValue("subTotal");
//                            createCell(subtitleRow, 8, formatPrice(debugFeeSubTotal), cellStyle);
//                            rowNum++;
//                        } else if ("回厂调试".equals(jlNameEnum.getItemName())) {
//                            Row debugRow = sheet.createRow(rowNum);
//                            debugRow.setHeight((short) 490);
//                            createCell(debugRow, 0, sequence, rowStyle);
//                            createCell(debugRow, 1, name, rowStyle);
//                            createCell(debugRow, 2, jlNameEnum.getItemName(), rowStyle);
//                            createCell(debugRow, 3, "工时（小时）", cellStyle);
//                            createCell(debugRow, 6, "单价（元/含税）", cellStyle);
//                            createCell(debugRow, 7, "金额（元/含税）", cellStyle);
//                            createCell(debugRow, 8, "小计（元/含税）", cellStyle);
//                            rowNum++;
//                        }
//                        if (jlNameEnum.getItemName().equals("试模")) {
//                            if (null != tryoutObj) {
//                                createCell(row, 2, jlNameEnum.getItemName(), rowStyle);
//                                createCell(row, 3, tryoutObj.getString("count"), cellStyle);
//                                createCell(row, 6, formatPrice(tryoutObj.getDoubleValue("price")), cellStyle);
//                                createCell(row, 7, formatPrice(tryoutObj.getDoubleValue("totalPrice")), cellStyle);
//                            }
//                        }
//                        if (jlNameEnum.getItemName().equals("回厂调试")) {
//                            if (null != debugObj) {
//                                createCell(row, 2, jlNameEnum.getItemName(), rowStyle);
//                                createCell(row, 3, debugObj.getString("hour"), cellStyle);
//                                createCell(row, 6, formatPrice(debugObj.getDoubleValue("price")), cellStyle);
//                                createCell(row, 7, formatPrice(debugObj.getDoubleValue("totalPrice")), cellStyle);
//                            }
//                        }
                    case "测量":
                        JSONObject measureFeeObj = detailData.getMeasureFee();
                        List<MeasureFee> measureFees = JSONArray.parseArray(String.valueOf(measureFeeObj.getJSONArray("dataList")), MeasureFee.class);
                        measureFeesize = measureFees.size();
                        for (MeasureFee measureFee : measureFees) {
                            Row row = ExcelUtil.getRow(sheet, rowNum);
                            row.setHeight((short) 490);
                            createCell(row, 2, measureFee.getItemName(), rowStyle);
                            createCell(row, 3, String.valueOf(measureFee.getHour()), cellStyle);
                            createCell(row, 6, formatPrice(measureFee.getPrice()), cellStyle);
                            createCell(row, 7, formatPrice(measureFee.getTotalPrice()), cellStyle);
                            rowNum++;
                        }
                        //小计
                        measureFeeSubTotal = measureFeeObj.getDoubleValue("subTotal");
                        createCell(firstrow, 8, formatPrice(measureFeeSubTotal), cellStyle);
                        break;
                    case "运输":
                        JSONObject transportFeeObj = detailData.getTransportFee();
                        List<TransportFee> transportFees = JSONArray.parseArray(String.valueOf(transportFeeObj.getJSONArray("dataList")), TransportFee.class);
                        transportFeesize = transportFees.size();
                        for (TransportFee transportFee : transportFees) {
                            Row row = ExcelUtil.getRow(sheet, rowNum);
                            row.setHeight((short) 490);
                            createCell(row, 2, transportFee.getItemName(), rowStyle);
                            createCell(row, 3, String.valueOf(transportFee.getWeight()), cellStyle);
                            createCell(row, 6, formatPrice(transportFee.getPrice()), cellStyle);
                            createCell(row, 7, formatPrice(transportFee.getTotalPrice()), cellStyle);
                            rowNum++;
                        }
                        //小计
                        transportFeeSubTotal = transportFeeObj.getDoubleValue("subTotal");
                        createCell(firstrow, 8, formatPrice(transportFeeSubTotal), cellStyle);
                        break;
                }
            }

            //合并单元格
            //原材料
            int firstRow = 3;
            int lastRow;
            if (rawMaterialsize == 0) {
                lastRow = firstRow;
            } else {
                lastRow = firstRow + rawMaterialsize - 1;
                if (rawMaterialsize > 1) {
                    mergeCells(sheet, firstRow, lastRow, 0, 0);
                    mergeCells(sheet, firstRow, lastRow, 1, 1);
                    mergeCells(sheet, firstRow, lastRow, 8, 8);
                }
            }
            //标准件
            firstRow = lastRow + 1;
            if (standardComponentsize == 0) {
                lastRow = firstRow + 1;
            } else {
                lastRow = firstRow + standardComponentsize;
                if (standardComponentsize > 1) {
                    mergeCells(sheet, firstRow + 1, lastRow, 8, 8);
                }
            }
            mergeCells(sheet, firstRow, lastRow, 0, 0);
            mergeCells(sheet, firstRow, lastRow, 1, 1);
            //人工
            firstRow = lastRow + 1;
            int hourStart = firstRow;
            if (artificialFeesize == 0) {
                lastRow = firstRow + 1;
            } else {
                lastRow = firstRow + artificialFeesize;
                if (artificialFeesize > 1) {
                    mergeCells(sheet, firstRow + 1, lastRow, 8, 8);
                }
            }
            mergeCells(sheet, firstRow, lastRow, 0, 0);
            mergeCells(sheet, firstRow, lastRow, 1, 1);
            //制造
            firstRow = lastRow + 1;
            if (manufacturingFeesize == 0) {
                lastRow = firstRow + 1;
            } else {
                lastRow = firstRow + manufacturingFeesize;
                if (manufacturingFeesize > 1) {
                    mergeCells(sheet, firstRow + 1, lastRow, 8, 8);
                }
            }
            mergeCells(sheet, firstRow, lastRow, 0, 0);
            mergeCells(sheet, firstRow, lastRow, 1, 1);
            //装配
            firstRow = lastRow + 1;
            if (assemblyFeesize == 0) {
                lastRow = firstRow + 1;
            } else {
                lastRow = firstRow + assemblyFeesize;
                if (assemblyFeesize > 1) {
                    mergeCells(sheet, firstRow + 1, lastRow, 8, 8);
                }
            }
            mergeCells(sheet, firstRow, lastRow, 0, 0);
            mergeCells(sheet, firstRow, lastRow, 1, 1);
            //调试
            firstRow = lastRow + 1;
            if (debugFeesize == 0) {
                lastRow = firstRow + 1;
            } else {
                lastRow = firstRow + debugFeesize;
                if (debugFeesize > 1) {
                    mergeCells(sheet, firstRow + 1, lastRow, 8, 8);
                }
            }
            mergeCells(sheet, firstRow, lastRow, 0, 0);
            mergeCells(sheet, firstRow, lastRow, 1, 1);
//            firstRow = lastRow + 1;
//            lastRow = firstRow + 3;
//            mergeCells(sheet, firstRow, lastRow, 0, 0);
//            mergeCells(sheet, firstRow, lastRow, 1, 1);
//            mergeCells(sheet, firstRow, firstRow + 1, 2, 2);
//            mergeCells(sheet, firstRow + 2, lastRow, 2, 2);
//            mergeCells(sheet, firstRow, lastRow, 8, 8);
            //测量
            firstRow = lastRow + 1;
            if (measureFeesize == 0) {
                lastRow = firstRow + 1;
            } else {
                lastRow = firstRow + measureFeesize;
                if (measureFeesize > 1) {
                    mergeCells(sheet, firstRow + 1, lastRow, 8, 8);
                }
            }
            mergeCells(sheet, firstRow, lastRow, 0, 0);
            mergeCells(sheet, firstRow, lastRow, 1, 1);
            //运输
            firstRow = lastRow + 1;
            if (transportFeesize == 0) {
                lastRow = firstRow + 1;
            } else {
                lastRow = firstRow + transportFeesize;
                if (transportFeesize > 1) {
                    mergeCells(sheet, firstRow + 1, lastRow, 8, 8);
                }
            }
            mergeCells(sheet, firstRow, lastRow, 0, 0);
            mergeCells(sheet, firstRow, lastRow, 1, 1);

            //工时列合并
            int hourEnd = lastRow + 1;
            for (int j = hourStart; j < hourEnd; j++) {
                mergeCells(sheet, j, j, 3, 5);
            }

            //利润
            Row nineRow = sheet.createRow(rowNum);
            createCell(nineRow, 0, "9", rowStyle);
            createCell(nineRow, 1, "利润", rowStyle);
            createCell(nineRow, 3, "占模具总价比例（%）", cellStyle);
            createCell(nineRow, 8, "小计（元/含税）", cellStyle);
            JSONObject profitObj = detailData.getProfitObj();
            Row nineRow2 = sheet.createRow(rowNum + 1);
            createCell(nineRow2, 0, "9", rowStyle);
            createCell(nineRow2, 1, "利润", rowStyle);
            createCell(nineRow2, 3, profitObj.getString("ratio"), cellStyle);
            createCell(nineRow2, 8, formatPrice(profitObj.getDoubleValue("subTotal")), cellStyle);
            firstRow = lastRow + 1;
            lastRow = firstRow + 1;
            mergeCells(sheet, firstRow, lastRow, 0, 0);
            mergeCells(sheet, firstRow, firstRow, 3, 7);
            mergeCells(sheet, firstRow, lastRow, 1, 2);
            mergeCells(sheet, lastRow, lastRow, 3, 7);
            //模具总价（元/含税）
            //计算模具总价
            double sumPrice = rawMaterialSubTotal
                    + standardComponentSubTotal
                    + artificialFeeSubTotal
                    + manufacturingFeeSubTotal
                    + assemblyFeeSubTotal
                    + debugFeeSubTotal
                    + measureFeeSubTotal
                    + transportFeeSubTotal;
            Row sumRow = sheet.createRow(rowNum + 2);
            createCell(sumRow, 0, "模具总价（元/含税）", rowStyle);
            createCell(sumRow, 3, formatPrice(detailData.getMouldFee()), cellStyle);
            firstRow = lastRow + 1;
            lastRow = firstRow;
            mergeCells(sheet, firstRow, firstRow, 0, 2);
            mergeCells(sheet, firstRow, firstRow, 3, 8);
            //最后一行 注：
            CellStyle lastCellStyle = createCellStyle(workbook, "微软雅黑", 11, true, false, HorizontalAlignment.LEFT);
            Row remarkRow = sheet.createRow(rowNum + 3);
            createCell(remarkRow, 0, "注：请如实填写，若此项无费用，请划“/”。", lastCellStyle);
            firstRow = lastRow + 1;
            mergeCells(sheet, firstRow, firstRow, 0, 8);
        }
        return PoiUtils.createExcelFile(workbook, filePath, fileName);
    }


    // 创建单元格样式
    private static CellStyle createCellStyle(Workbook workbook, String fontName, int fontSize, boolean isBold, boolean isWrapText, HorizontalAlignment horizontalAlignment) {
        CellStyle style = workbook.createCellStyle();
        Font font = workbook.createFont();
        font.setFontName(fontName);
        font.setFontHeightInPoints((short) fontSize);
        font.setBold(isBold);
        style.setWrapText(isWrapText);
        style.setFont(font);
        style.setAlignment(horizontalAlignment);
        style.setVerticalAlignment(VerticalAlignment.CENTER);
        style.setBorderBottom(BorderStyle.THIN);
        style.setBorderTop(BorderStyle.THIN);
        style.setBorderLeft(BorderStyle.THIN);
        style.setBorderRight(BorderStyle.THIN);
        return style;
    }

    // 设置单元格样式
    private static void setCellStyle(Cell cell, CellStyle style, String position) {
        cell.setCellStyle(style);
        if ("left".equals(position)) {
            style.setAlignment(HorizontalAlignment.LEFT);
        }
        if ("right".equals(position)) {
            style.setAlignment(HorizontalAlignment.RIGHT);
        }
        if ("center".equals(position)) {
            style.setAlignment(HorizontalAlignment.CENTER);
        }
    }

    // 合并单元格
    private static void mergeCells(Sheet sheet, int firstRow, int lastRow, int firstCol, int lastCol) {
        CellRangeAddress mergedRegion = new CellRangeAddress(firstRow, lastRow, firstCol, lastCol);
        RegionUtil.setBorderTop(BorderStyle.THIN, mergedRegion, sheet);
        RegionUtil.setBorderRight(BorderStyle.THIN, mergedRegion, sheet);
        RegionUtil.setBorderBottom(BorderStyle.THIN, mergedRegion, sheet);
        RegionUtil.setBorderLeft(BorderStyle.THIN, mergedRegion, sheet);
        sheet.addMergedRegion(mergedRegion);
    }

    //创建单元格并设置值和样式
    private static void createCell(Row row, int columnIndex, String value, CellStyle style) {
        Cell cell = row.createCell(columnIndex);
        cell.setCellValue(value);
        cell.setCellStyle(style);
    }

    private static DecimalFormat df = new DecimalFormat("0.00");

    private static String formatPrice(double num) {
        return "￥" + df.format(num);
    }

}
